#!/usr/bin/env python
# -*- coding: utf-8 -*-
""" Database create, read, update and delete operations via WSGI and JSON.


TODO: support filtering and paging in readrecs.  use query string params.

TODO: support db relation attribute names lookup and cache for
pre-computed insert and update statements.  need to adjust interface
for connection callable as param.


"""
from logging import debug


from tack.db import query
from tack.lib import content_headers, not_found, string_app
from tack.lib.jsontools import json_dumps


text_plain = content_headers.text_plain


def addrec(relation, fields, formatter=json_dumps, content_type=text_plain):
    """ Creates an app that inserts a database record using values from HTTP input.

    The created app returns value of insereted primary key if possible.

    Given the relation and list of fields, the app assembles an insert
    statement based on the key/value pairs that are posted to the
    request. Only the field names that are passed when the app is
    created are inserted, and only the keys in the post request are put
    in the insert statement, relying on the database schema for default
    values for the other fields.

    @param relation database table, view or other relation name
    @keyparam fields=None name of the fields to be inserted
    @keyparam formatter=json_dumps function to format the data for return to the client
    @keyparam content_type=('Content-Type', 'text/plain')
    @return new WSGI application
    """
    prefix = "INSERT INTO %s" % relation

    def addrec_inner(env, respond):
        form = env['form']
        postvalues = dict([(k, form[k]) for k in form])
        cols = [f for f in fields if f in postvalues]
        vals = [postvalues[f] for f in fields if f in postvalues]
        attrnames = "('%s')" % str.join("', '", cols)
        attrfs = "(%s)" % str.join(", ", ['%s']*len(cols))
        sql = '%s %s VALUES %s' % (prefix, attrnames, attrfs)
        debug("addrec query: %s", sql)
        output = formatter(query(env['connection'], sql, *vals))
        return string_app(output, content_type=content_type)(env, respond)
    return addrec_inner


def delrec(relation, key, formatter=json_dumps, content_type=text_plain):
    """ Creates an app that deletes a database record using HTTP query.

    Given the relation and key, the app will construct the query, and
    use the provided key and value (pulled from the environ) to delete
    a record in the given relation.

    @param relation database table, view or other relation name    
    @param key key on which to filter for the delete
    @keyparam formatter=json_dumps function to format the data for return to the client
    @keyparam content_type=('Content-Type', 'text/plain')
    @return new WSGI application
    """
    sql = 'DELETE FROM "%s" WHERE "%s" = %%s' % (relation, key)

    def delrec_inner(env, respond):
        debug("delrec query %s" % sql)
        output = formatter(query(env['connection'], sql, env[key]))
        return string_app(output, content_type=content_type)(env, respond)
    return delrec_inner


def modrec(relation, key, fields, formatter=json_dumps,
           content_type=text_plain):
    """ Creates an app that updates a database record using HTTP input and query.

    Because we can't guarentee dict order, any SQL string passed to this
    function CANNOT have placeholders.  But of course, assembling an
    entire SQL on one's own is not advised, so the sql parameter probably
    won't be used at all.

    Given the relation, a key and list of fields, the app assembles an
    update statement based on the key/value pairs that are posted to the
    request. Only the field names that are passed when the app is
    created are inserted, and only the values in the post request are
    put in the update statement.


    @param relation database table, view or other relation name
    @keyparam sql=None database query string
    @keyparam key= None key of record being modified
    @keyparam fields=None name of the fields to be inserted
    @keyparam formatter=json_dumps function to format the data for return to the client
    @keyparam content_type=('Content-Type', 'text/plain')
    @return new WSGI application
    """
    prefix = 'UPDATE %s SET' % relation

    def modrec_inner(env, respond):
        form = env['form']
        postvs = dict([(k, form[k]) for k in form])
        cols = [f for f in fields if f in postvs]
        vals = [postvs[f] for f in fields if f in postvs] + [env[key]]
        update_attrs = ','.join(["%s=%%s" % f for f in cols])
        where_clause = "%s = %%s" % (key, )
        sql = '%s %s WHERE %s' % (prefix, update_attrs, where_clause)
        debug("modrec query: %s", sql)
        output = formatter(query(env['connection'], sql, *vals))
        return string_app(output, content_type=content_type)(env, respond)
    return modrec_inner


def readrec(relation, key, formatter=json_dumps, content_type=text_plain,
            value_lookup=None,
            cols='*',
            wheres='',
            handle404=not_found):
    """ Creates an app that returns a single database record.
    Given the relation and key, the record_reader app will construct the
    query, and use the provided key to pull the value from the environment.

    @param relation database table, view or other relation name
    @param key key for the where clause
    @keyparam content_type=('Content-Type', 'text/plain')
    @return new WSGI application
    """
    sql = 'SELECT %s FROM "%s" WHERE "%s" = %%s %s' % (cols, relation, key, wheres)

    if not callable(value_lookup):
        value_lookup = lambda e:e[key]

    def readrec_inner(env, respond):
        debug("readrec query: %s", sql)
        rows = query(env['connection'], sql, value_lookup(env))
        if not rows:
            debug("readrec not found: %s", sql)
            return handle404(env, respond)
        output = formatter(rows[0])
        return string_app(output, content_type=content_type)(env, respond)
    return readrec_inner


def readrecs(relation, formatter=json_dumps, content_type=text_plain,
             cols='*', wheres=''):
    """ Creates an app that returns a set of database records.

    @param relation database table, view or other relation name
    @keyparam content_type=('Content-Type', 'text/plain')
    @return new WSGI application
    """
    sql = 'SELECT %s FROM "%s" WHERE 1=1 %s' % (cols, relation, wheres)

    def readrecs_inner(env, respond):
        debug("readrecs query: %s", sql)
        output = formatter(query(env['connection'], sql))
        return string_app(output, content_type=content_type)(env, respond)
    return readrecs_inner
